/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package config;

import GUI.Staff_login;
import com.toedter.calendar.JDateChooser;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

/**
 *
 * @author RYUDO
 */
public class User_function {

    public static String get_role(String get_selected_role,String username,String password) {
        String role = "";
        try {
            if(get_selected_role.equals("Administrator")) {
                Connection cn = Database_function.setConnection();
                Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
                ResultSet rs = st.executeQuery("SELECT TOP (1) Username,Password FROM tbl_Admin WHERE Username = '"+username+"' AND Password = '"+password+"'");
                rs.first();
                if(username.equals(rs.getString("Username").trim()) && password.equals(rs.getString("Password").trim())) {
                    return role = "Administrator";
                }
            } else if(get_selected_role.equals("Staff")) {
                Connection cn = Database_function.setConnection();
                Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
                ResultSet rs = st.executeQuery("SELECT TOP (1) Username,Password FROM tbl_Staff WHERE Username = '"+username+"' AND Password = '"+password+"'");
                rs.first();
                if(username.equals(rs.getString("Username").trim()) && password.equals(rs.getString("Password").trim())) {
                    return role = "Staff";
                }               
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return role;

    }

    public static void log_in(String table,String username) {
        try {
            Connection cn = Database_function.setConnection();
            Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            st.executeUpdate("UPDATE "+table+" SET Logged_in = 'TRUE' WHERE Username = '"+username+"'");
        } catch (SQLException ex) {
            Logger.getLogger(User_function.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static boolean log_out(String table,String username,boolean force) {
        boolean exit = false;
        try {
            if(!force) {
                int response = JOptionPane.showConfirmDialog(null, "Are you sure to exit the main program ?","Confirm",JOptionPane.YES_NO_OPTION,JOptionPane.QUESTION_MESSAGE);
                if(response == JOptionPane.YES_OPTION) {
                    Connection cn = Database_function.setConnection();
                    Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
                    st.executeUpdate("UPDATE "+table+" SET Logged_in = 'FALSE' WHERE username = '"+username+"'");
                    Staff_login sl = new Staff_login();
                    sl.setVisible(true);
                    exit = true;
                }
            } else {
                Connection cn = Database_function.setConnection();
                Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
                st.executeUpdate("UPDATE "+table+" SET Logged_in = 'FALSE' WHERE username = '"+username+"'");
                Staff_login sl = new Staff_login();
                sl.setVisible(true);
                exit = true;
            }

        } catch (SQLException ex) {
            Logger.getLogger(User_function.class.getName()).log(Level.SEVERE, null, ex);
        }
        return exit;
    }

    public static ResultSet get_staff_info(String username,String table) {
        ResultSet rs = null;
        try {            
            Connection cn = Database_function.setConnection();
            Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            rs = st.executeQuery("SELECT [Username],[Firstname],[Lastname],[Phone],[Gender],[Birthday],[Startdate],[Address],[Email],[Password] FROM "+table+" WHERE Username = '"+username+"'");       
            if(!rs.wasNull()) {
                rs.first();             
            }
        } catch (SQLException ex) {
            Logger.getLogger(User_function.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
        
    }

    public static void change_staff_password(String table,String username,String password) {
        try {
            int response = JOptionPane.showConfirmDialog(null, "Are you sure to change the password ?","Confirm",JOptionPane.YES_NO_OPTION,JOptionPane.QUESTION_MESSAGE);
            if(response == JOptionPane.YES_OPTION) {
                Connection cn = Database_function.setConnection();
                Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                st.executeUpdate("UPDATE "+table+" SET Password = '"+password+"' WHERE username = '"+username+"'");
                JOptionPane.showMessageDialog(null, "Update Successfully ! You must re-login to take effect");
                User_function.log_out(table, username, true);
            }
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, "Error with the database, update password failed");
        }

    }

    public static void delete_staff(String table,String username) {
        try {
            int response = JOptionPane.showConfirmDialog(null, "Are you sure to delete this user ?","Confirm",JOptionPane.YES_NO_OPTION,JOptionPane.QUESTION_MESSAGE);
            if(response == JOptionPane.YES_OPTION) {
                Connection cn = Database_function.setConnection();
                Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                if(st.executeUpdate("DELETE FROM "+table+" WHERE Username = '"+username+"' AND Logged_in = FALSE") != 0) {
                    JOptionPane.showMessageDialog(null, "User deleted successfully");
                } else {
                    JOptionPane.showMessageDialog(null, "No user was deleted, make sure that the user is not logged in");
                }
            }
        } catch (SQLException ex) {
           JOptionPane.showMessageDialog(null, "Error Deleting User");
        }
    }

     public static String check_role(String role){
        if(role.equals("Administrator")) {
            return "tbl_Admin";
        } else {
            return "tbl_Staff";
        }
    }

     public static String get_age_group_name_by_id(String AgeGroupID) {
         String ID = null;
         try {
            Connection cn = Database_function.setConnection();
            Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = st.executeQuery("SELECT AgeGroup FROM tbl_AgeGroup WHERE AgeGroupID = "+AgeGroupID);
            rs.first();
            ID = rs.getString(1);           
        } catch (SQLException ex) {
            Logger.getLogger(User_function.class.getName()).log(Level.SEVERE, null, ex);
        }
         return ID;
     }

      public static ResultSet get_age_group() {
        ResultSet res = null;
         try {
            Connection cn = Database_function.setConnection();
            Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            res = st.executeQuery("SELECT AgeGroup FROM tbl_AgeGroup ORDER BY AgeGroupID ASC");
            
        } catch (SQLException ex) {
            Logger.getLogger(User_function.class.getName()).log(Level.SEVERE, null, ex);
        }
         return res;
     }

      public static String get_age_group_id_by_name(String AgeGroupName) {
         String AGName = null;
         try {
            Connection cn = Database_function.setConnection();
            Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = st.executeQuery("SELECT AgeGroupID FROM tbl_AgeGroup WHERE AgeGroup = '"+AgeGroupName+"'");
            rs.first();
            AGName = rs.getString(1);
        } catch (SQLException ex) {
            Logger.getLogger(User_function.class.getName()).log(Level.SEVERE, null, ex);
        }
         return AGName;
     }

       public static ResultSet get_child_info_by_id(String ChildID) {
             ResultSet rs = null;
             try {
                Connection cn = Database_function.setConnection();
                Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                rs = st.executeQuery("SELECT * FROM tbl_Child WHERE ChildID = "+ChildID+" AND Status = 'TRUE'");

            } catch (SQLException ex) {
                Logger.getLogger(User_function.class.getName()).log(Level.SEVERE, null, ex);
            }
             return rs;
        }

     

        public static String get_activity_id_by_name(String ActivityName) {
        String id = null;
         try {
            Connection cn = Database_function.setConnection();
            Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = st.executeQuery("SELECT ActivityID FROM tbl_Activity WHERE ActivityName = '"+ActivityName+"'");
            rs.first();
            id = rs.getString(1);
        } catch (SQLException ex) {
            Logger.getLogger(User_function.class.getName()).log(Level.SEVERE, null, ex);
        }
        return id;
     }

        public static String get_activity_name_by_id(String ActivityID) {
        String id = null;
         try {
            Connection cn = Database_function.setConnection();
            Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = st.executeQuery("SELECT ActivityName FROM tbl_Activity WHERE ActivityID = '"+ActivityID+"'");
            rs.first();
            id = rs.getString(1);
        } catch (SQLException ex) {
            Logger.getLogger(User_function.class.getName()).log(Level.SEVERE, null, ex);
        }
        return id;
     }


         public static ResultSet get_activity_info_by_AgeGroupID(String AgeGroupID) {
       ResultSet rs = null;
         try {
            Connection cn = Database_function.setConnection();
            Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = st.executeQuery("SELECT * FROM tbl_Activity WHERE AgeGroupID = '"+AgeGroupID+"'");
            
           
        } catch (SQLException ex) {
            Logger.getLogger(User_function.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
     }

        public static ResultSet get_nanny_info_by_id(String NannyID) {
             ResultSet rs = null;
             try {
                Connection cn = Database_function.setConnection();
                Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                rs = st.executeQuery("SELECT * FROM tbl_Nanny WHERE NannyID = "+NannyID+" AND Status = 'TRUE'");

            } catch (SQLException ex) {
                Logger.getLogger(User_function.class.getName()).log(Level.SEVERE, null, ex);
            }
             return rs;
        }

        public static ResultSet get_tbl_Child_Nanny(String ChildID, String NannyID ){
            ResultSet rs = null;
            try {
                
                Connection cn = Database_function.setConnection();
                Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                rs = st.executeQuery("SELECT * FROM tbl_Child_Nanny WHERE NannyID = " + NannyID + " AND ChildID = "+ChildID+"");
            } catch (SQLException ex) {
                Logger.getLogger(User_function.class.getName()).log(Level.SEVERE, null, ex);
            }
            return rs;
        }

         public static ResultSet get_tbl_Child_Nanny_all(){
            ResultSet rs = null;
            try {

                Connection cn = Database_function.setConnection();
                Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                rs = st.executeQuery("SELECT * FROM tbl_Child_Nanny");
            } catch (SQLException ex) {
                Logger.getLogger(User_function.class.getName()).log(Level.SEVERE, null, ex);
            }
            return rs;
        }

         public static ResultSet get_tbl_Activity_Child(String ChildID){
            ResultSet rs = null;
            try {

                Connection cn = Database_function.setConnection();
                Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                rs = st.executeQuery("SELECT * FROM tbl_Activity_Child WHERE ChildID = " + ChildID);
                rs.beforeFirst();
            } catch (SQLException ex) {
                Logger.getLogger(User_function.class.getName()).log(Level.SEVERE, null, ex);
            }
            return rs;
        }

         public static String get_activity_fee(String ActivityID) {
             String fee = null;
            try {

                Connection cn = Database_function.setConnection();
                Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                ResultSet rs = st.executeQuery("SELECT Fee FROM tbl_Activity WHERE ActivityID = "+ActivityID);
                rs.first();
                fee = rs.getString(1);
            } catch (SQLException ex) {
                Logger.getLogger(User_function.class.getName()).log(Level.SEVERE, null, ex);
            }
             return fee;
         }

         public static String get_agegroup_fee(String AgeGroupID) {
             String fee = null;
            try {

                Connection cn = Database_function.setConnection();
                Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                ResultSet rs = st.executeQuery("SELECT Fee FROM tbl_AgeGroup WHERE AgeGroupID = "+AgeGroupID);
                rs.first();
                fee = rs.getString(1);
            } catch (SQLException ex) {
                Logger.getLogger(User_function.class.getName()).log(Level.SEVERE, null, ex);
            }
             return fee;
         }

         public static String get_nanny_fee(String NannyID) {
             String fee = null;
            try {

                Connection cn = Database_function.setConnection();
                Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                ResultSet rs = st.executeQuery("SELECT Charge = Charge * WorkingHours FROM tbl_Nanny WHERE NannyID = "+NannyID);
                rs.first();
                fee = rs.getString(1);
            } catch (SQLException ex) {
                Logger.getLogger(User_function.class.getName()).log(Level.SEVERE, null, ex);
            }
             return fee;
         }

         public static ResultSet search_contract (String searchString, String searchType){
            ResultSet rs = null;
            try {

                Connection cn = Database_function.setConnection();
                Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                rs = st.executeQuery("SELECT * FROM tbl_Child_Nanny WHERE "+searchType+" like '%"+searchString+"%'");
            } catch (SQLException ex) {
                Logger.getLogger(User_function.class.getName()).log(Level.SEVERE, null, ex);
            }
            return rs;
        }

        public static ResultSet advanced_search_contract (String searchString, String searchType, JDateChooser txtStartDate, JDateChooser txtEndDate){
            ResultSet rs = null;
            try {
                
                Connection cn = Database_function.setConnection();
                Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                Date DateS = txtStartDate.getDate();
                Date DateE = txtEndDate.getDate();
                
                String Query="SELECT * FROM tbl_Child_Nanny";
                Query+= " WHERE ("+searchType+" like '%"+searchString+"%') ";
                
                // check null date field
                if (DateS==null)
                    DateS = new Date(0000,00,01);
                if (DateE==null)
                    DateE = new Date(8099,00,11);

                // build search date string
                String StringS= String.valueOf(DateS.getYear()+1900) + "-" + String.valueOf(DateS.getMonth()+1) + "-" + String.valueOf(DateS.getDate()) ;
                String StringE= String.valueOf(DateE.getYear()+1900) + "-" + String.valueOf(DateE.getMonth()+1) + "-" + String.valueOf(DateE.getDate()) ;
                
                // validate and define date condition
                if (DateS.before(DateE)==false)
                    JOptionPane.showMessageDialog(null, "Start date cannot be more than end date! ","ERROR",JOptionPane.ERROR_MESSAGE);
                else
                    Query+="and ((Startdate >= '"+StringS+"') and (Enddate <= '"+StringE+"'))";

                Query+="order by "+searchType+" desc";
                rs = st.executeQuery(Query);
                
            } catch (SQLException ex) {
                Logger.getLogger(User_function.class.getName()).log(Level.SEVERE, null, ex);
            }
            return rs;
        }

}
